﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Web;
using System.Web.UI.WebControls;
using System.Web.UI;
using System.IO;
using System.Collections;
using System.Data.OleDb;
using System.Data;
using System.Data.SqlClient;
using System.Reflection;

namespace BIZ
{
    //调用：
    //ExcelOper.ExportToExcel(tb, new string[] { "id", "name" },  new string[] { "id","姓名"}); 
   public static class ExcelOper
    {
        public static void ExportToExcel(this string stream, HttpContextBase context)
        {
            context.Response.ClearContent();
            context.Response.Buffer = true;
            context.Response.ContentType = "application/vnd.ms-excel";
            context.Response.ContentEncoding = Encoding.UTF8;
            context.Response.Charset = "utf-8";
            context.Response.AddHeader("content-disposition", string.Format("attachment; filename=ExportExcelFile{0}.xls", DateTime.Now.ToString("yyyyMMddHHmmss")));

            //CheckStreamHtmlHeader(ref stream);

            context.Response.Write(stream);
            context.Response.End();
        }
        public static void ExportToExcel<TEntity>(this IList<TEntity> dataSource, HttpContextBase context)
        {
            context.Response.ClearContent();
            context.Response.Buffer = true;
            context.Response.ContentType = "application/vnd.ms-excel";
            context.Response.ContentEncoding = Encoding.UTF8;
            context.Response.Charset = "utf-8";
            context.Response.AddHeader("content-disposition", string.Format("attachment; filename=ExportExcelFile{0}.xls", DateTime.Now.ToString("yyyyMMddHHmmss")));

            GridView view = new GridView();
            view.DataSource = dataSource;
            view.DataBind();

            StringWriter writer = new StringWriter();
            HtmlTextWriter writer2 = new HtmlTextWriter(writer);
            view.RenderControl(writer2);
            string str = writer.ToString();

            //CheckStreamHtmlHeader(ref str);

            context.Response.Write(str);
            context.Response.End();
        }

        /// <summary>
        /// 将集合类转换成DataTable
        /// </summary>
        /// <param name="list">集合</param>
        /// <returns></returns>
        public static DataTable ToDataTable(IList list)
        {
            DataTable result = new DataTable();
            if (list.Count > 0)
            {
                PropertyInfo[] propertys = list[0].GetType().GetProperties();
                foreach (PropertyInfo pi in propertys)
                {
                    result.Columns.Add(pi.Name, pi.PropertyType);
                }
                    
                for (int i = 0; i < list.Count; i++)
                {
                    ArrayList tempList = new ArrayList();
                    foreach (PropertyInfo pi in propertys)
                    {
                        object obj = pi.GetValue(list[i], null);
                        tempList.Add(obj);
                    }
                    object[] array = tempList.ToArray();
                    result.LoadDataRow(array, true);
                }
            }
            return result;
        }

        public static void ExportToExcel(DataTable dataList, string[] fields, string[] headTexts, string title)
        {
            GridView gvw = new GridView();
            int ColCount, i;

            //如果筛选的字段和对应的列头名称个数相对的情况下只导出指定的字段
            if (fields.Length != 0 && fields.Length == headTexts.Length)
            {
                ColCount = fields.Length;
                gvw.AutoGenerateColumns = false;

                for (i = 0; i < ColCount; i++)
                {
                    BoundField bf = new BoundField();
                    bf.DataField = fields[i];
                    bf.HeaderText = headTexts[i];
                    gvw.Columns.Add(bf);
                }
            }
            else
            {
                gvw.AutoGenerateColumns = true;
            }

            SetStype(gvw);
            gvw.DataSource = dataList;
            gvw.DataBind();

            ExportToExcel(gvw, title);
        }
        /// <summary>
        /// 导出数据到Excel
        /// </summary>
        /// <param name="DataList">IList Data</param>
        /// <param name="Fields">要导出的字段</param>
        /// <param name="HeadName">字段对应显示的名称</param>
        public static void ExportToExcel(DataTable dataList, string[] fields, string[] headTexts)
        {
            ExportToExcel(dataList, fields, headTexts, string.Empty);
        }

        /// <summary>
        /// 设置样式
        /// </summary>
        /// <param name="gvw"></param>
        private static void SetStype(GridView gvw)
        {
            gvw.Font.Name = "Verdana";
            gvw.BorderStyle = System.Web.UI.WebControls.BorderStyle.Solid;
            gvw.HeaderStyle.BackColor = System.Drawing.Color.LightCyan;
            gvw.HeaderStyle.ForeColor = System.Drawing.Color.Black;
            gvw.HeaderStyle.HorizontalAlign = System.Web.UI.WebControls.HorizontalAlign.Center;
            gvw.HeaderStyle.Wrap = false;
            gvw.HeaderStyle.Font.Bold = true;
            gvw.HeaderStyle.Font.Size = 10;
            gvw.RowStyle.Font.Size = 10;
        }
        /// <summary>
        /// 导出GridView中的数据到Excel
        /// </summary>
        /// <param name="gvw"></param>
        /// <param name="DataList"></param>
        private static void ExportToExcel(GridView gvw, string title)
        {
            string fileName;
            HttpContext.Current.Response.Buffer = true;
            HttpContext.Current.Response.ClearContent();
            HttpContext.Current.Response.ClearHeaders();
            fileName = string.Format("joy {0:yyyy-MM-dd_HH_mm}.xls", DateTime.Now);
            HttpContext.Current.Response.AppendHeader("Content-Disposition", "attachment;filename=" + fileName);
            HttpContext.Current.Response.ContentType = "application/vnd.ms-excel";
            HttpContext.Current.Response.ContentEncoding = Encoding.UTF8;
            HttpContext.Current.Response.Charset = "utf-8";
            HttpContext.Current.Response.Write("<meta http-equiv=Content-Type content=\"text/html; charset=utf-8\">");
            StringWriter tw = new System.IO.StringWriter();
            HtmlTextWriter hw = new System.Web.UI.HtmlTextWriter(tw);
            gvw.RenderControl(hw);
            if (!string.IsNullOrEmpty(title))
            {
                HttpContext.Current.Response.Write("<b><center><font size=3 face=Verdana color=#0000FF>" + title + "</font></center></b>");
            }
            HttpContext.Current.Response.Write(tw.ToString());
            HttpContext.Current.Response.Flush();
            HttpContext.Current.Response.Close();
            HttpContext.Current.Response.End();

            gvw.Dispose();
            tw.Dispose();
            hw.Dispose();

            gvw = null;
            tw = null;
            hw = null;

        }

       //加载Excel    
        public static DataTable LoadDataFromExcel(string filePath)  
        {  
            try  
            {  
                string strConn;  
                strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties='Excel 8.0;HDR=False;IMEX=1'";  
                OleDbConnection OleConn = new OleDbConnection(strConn);  
                OleConn.Open();  
                String sql = "SELECT * FROM [Sheet1$]";//可是更改Sheet名称，比如sheet2，等等    
  
                OleDbDataAdapter OleDaExcel = new OleDbDataAdapter(sql, OleConn);  
                DataSet OleDsExcle = new DataSet();  
                OleDaExcel.Fill(OleDsExcle, "Sheet1");  
                OleConn.Close();  
                return OleDsExcle.Tables[0];  
            }  
            catch (Exception err)  
            {  
                return new DataTable();  
            }  
        }  

    }

}
